package com.jy.api.dao;

import com.jy.api.domain.LeaseProduct;
import com.jy.api.domain.LeaseProductOrder;
import com.jy.api.domain.LeaseProductOrderType;
import com.jy.api.domain.OrderStatus;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.Date;
import java.util.List;

public interface LeaseProductOrderDao extends JpaRepository<LeaseProductOrder, Integer>, JpaSpecificationExecutor<LeaseProductOrder> {
    List<LeaseProductOrder> findByStoreId(String id);

    LeaseProductOrder findByStoreIdAndNumIdAndStatusInAndProductId(String storeId, int numId, List<OrderStatus> orderStatuses,int productId);



    List<LeaseProductOrder> findByNumIdAndStatusInAndStoreIdOrderByIdDesc(int numId, List<OrderStatus> orderStatuses, String storeId);

    LeaseProductOrder findByOrderNum(String orderId);

    Page<LeaseProductOrder> findByStoreIdAndStatus(String storeId, OrderStatus unfinished, Pageable pageable);

    @Query(value = "SELECT ifnull(SUM(get_amount),0),ifnull(SUM(refund_amount),0),ifnull(SUM(total_amount),0)    FROM lease_product_order WHERE `store_id` =? AND `refund_time` >=? and `refund_time` <=? and `status` ='REFUNDED' ", nativeQuery = true)
    List<Object[]> sumByStoreId(String storeId, Date beginTime, Date endTime);
    @Query(value = "SELECT ifnull(SUM(get_amount),0) FROM lease_product_order WHERE `store_id` =? AND `refund_time` >=? and `refund_time` <=? and `status` ='REFUNDED' ", nativeQuery = true)
    double sumGetAmount(String storeId, Date beginTime, Date endTime);
    @Query(value = "SELECT ifnull(SUM(get_amount),0) ,`channel` ,count(id) FROM lease_product_order WHERE `store_id` =? AND `refund_time` >=? and `refund_time` <=? and `status` ='REFUNDED' group by channel", nativeQuery = true)
    List<Object[]> sumGetAmountGroupByChannel(String storeId, Date beginTime, Date endTime);

    LeaseProductOrder findByNumNameLikeAndProductIdAndStatusInOrderByIdDesc(String oriNumName, int productId, List<OrderStatus> asList);

    @Query(value = "SELECT`product_name`,sum(get_amount),SUM(TIMESTAMPDIFF(MINUTE, `add_time`, `refund_time`)) AS minm FROM `lease_product_order` WHERE  `store_id` = ?  AND `refund_time` >=? and `refund_time` <=? and `status` ='REFUNDED' GROUP BY product_id ;", nativeQuery = true)
    List<Object[]> sumdata(String storeId, Date beginTime, Date endTime);
}
